﻿Imports MySql.Data

Public Class AccessDB

    Public ConnStr As String, conn As MySqlClient.MySqlConnection

    Public Sub New()

        Dim DBName As String, DBHost As String, DBPass As String, DBUser As String

        DBHost = "127.0.0.1"
        DBName = "horas"
        DBUser = "felipe.underlost"
        DBPass = "fxp010203"

        Me.ConnStr = "Server=" & DBHost & ";"
        Me.ConnStr &= "Database=" & DBName & ";"
        Me.ConnStr &= "Uid=" & DBUser & ";"
        Me.ConnStr &= "Pwd=" & DBPass & ";"
        Me.conn = New MySqlClient.MySqlConnection
        Me.conn.ConnectionString = Me.ConnStr

    End Sub

    Public Shared Function changeLastLetter(ByVal text As String, ByVal chToPutLast As String)

        Return Mid(text, 1, text.Length - 1) & chToPutLast

    End Function

    Public Shared Function returnValueString(ByVal value As String, ByVal type As String) As String

        Dim valueString As String, dateExploded() As String

        valueString = ""

        Select Case type
            Case "text"
                valueString = "'" & value & "'"
            Case "float"
                If value <> 0 Then
                    For textCount As Integer = 1 To value.Length
                        If Mid(value, textCount, 1) = "," Then
                            value = Mid(value, 1, textCount - 1) & "." & Mid(value, textCount + 1, value.Length - textCount)
                        End If
                    Next
                    valueString = value
                End If
            Case "date"
                If value <> "00:00:00" Then
                    dateExploded = value.Split("/")
                    valueString = "'" & dateExploded(2) & "/" & dateExploded(1) & "/" & dateExploded(0) & "'"
                End If
            Case Else
                valueString = "'" & value & "'"
        End Select

        Return valueString

    End Function

    Public Function returnFieldsString(ByVal args As Abstract_Attribute_Collection) As String

        Dim FieldsString As String, ValuesString As String

        FieldsString = "("
        ValuesString = " VALUES ("

        For Each arg As AbstractAttribute In args.attributes
            If (arg.isDbAtt) Then
                FieldsString &= arg.getData("field") & ","
                ValuesString &= arg.attV() & ","
            End If
        Next

        FieldsString = changeLastLetter(FieldsString, ")")
        ValuesString = changeLastLetter(ValuesString, ")")

        FieldsString &= ValuesString

        Return FieldsString

    End Function

    Public Function returnSelect(ByVal tableName As String, Optional ByVal fields As String = "*", Optional ByVal loadByArgs As String = "0") As DataTable

        Dim query_str As String, comm As New MySqlClient.MySqlCommand, mysqlAdapt As New MySqlClient.MySqlDataAdapter, returnTable As New DataTable

        query_str = "SELECT " & fields & " FROM " & tableName

        If loadByArgs <> "0" Then
            query_str &= " WHERE " & loadByArgs
        End If

        Me.conn.Open()
        comm.Connection = Me.conn
        comm.CommandText = query_str
        mysqlAdapt.SelectCommand = comm
        mysqlAdapt.Fill(returnTable)
        Me.conn.Close()

        Return returnTable

    End Function

    Public Function populate(ByVal tableName As String, ByVal fields As String) As String

        Dim query_str As String, comm As New MySqlClient.MySqlCommand

        query_str = "INSERT INTO " & tableName & fields
        Me.conn.Open()
        comm.Connection = conn
        comm.CommandText = query_str
        comm.ExecuteNonQuery()
        conn.Close()

        Return "paxo"

    End Function

    Public Function populate(ByVal data As Object) As String

        Dim query_str As String, comm As New MySqlClient.MySqlCommand

        query_str = "INSERT INTO " & data.DbInfo.ToString() & " " & returnFieldsString(data.attCollection)
        Me.conn.Open()
        comm.Connection = conn
        comm.CommandText = query_str
        comm.ExecuteNonQuery()
        conn.Close()

        Return "paxo"

    End Function

End Class
